package com.shengruan.admin.jdbc;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.shengruan.common.Constants;
import org.apache.commons.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.shengruan.admin.vo.Product;
import com.shengruan.framework.jdbc.BaseJdbc;
import com.shengruan.framework.jdbc.BeanHandler;
import com.shengruan.framework.jdbc.BeanListHandler;
import com.shengruan.index.action.AuctionUtil;

public class ProductJdbc  extends BaseJdbc{

    Logger logger = LoggerFactory.getLogger(UserJdbc.class);


    public static final String PRODUCT_INSERT = "insert into product (classId, name, auctionStatus, creationTime, adminId, imagePath, description, isvalid, smallPic,sort, dimension, globalLimited,specification, material, price)VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    /***
     * 获得记录个数
     * @param name
     * @param classId
     * @param isvalid
     * @param auctionStatus
     * @return
     */
    public int getRows(String name, String classId, String isvalid, String auctionStatus) {
        Connection conn = this.getConnection();
        StringBuilder sql = new StringBuilder("select count(id) from product where 1=1");

        if(StringUtils.isNotEmpty(classId)) sql.append(" and classId = " + classId);
        if(StringUtils.isNotEmpty(isvalid)) sql.append(" and isvalid = " + isvalid);
        if(StringUtils.isNotEmpty(auctionStatus)) sql.append(" and auctionStatus = " + auctionStatus);
        if (StringUtils.isNotEmpty(name)) sql.append(" and name like '%"+StringUtils.defaultString(name)+"%'");

        int count = 0;
		try {
			count = this.getRows(conn, sql.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
		return count;
    }

    public int getMaxSort() {
        Connection conn = this.getConnection();
        StringBuilder sql = new StringBuilder("select (max(p.sort)+1) as maxSort from product p");
        int count = 0;
		try {
			count = this.getRows(conn, sql.toString());
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
		return count;
    }


    /**
     * 获得商品列表
     *
     * @param pageSize
     * @param currentPage
     * @param name
     * @return
     */
    public List<Product> findWithPage(int currentPage, int pageSize, String name, String classId, String isvalid, String auctionStatus) {
        List<Product> productList = new ArrayList<Product>();
        Connection conn = this.getConnection();
        StringBuilder sql = new StringBuilder("SELECT p.*,pc.name AS className FROM product p LEFT JOIN product_class pc ON p.classId = pc.id where 1=1");

        if(StringUtils.isNotEmpty(classId)) sql.append(" and p.classId = " + classId);
        if(StringUtils.isNotEmpty(isvalid)) sql.append(" and p.isvalid = " + isvalid);
        if(StringUtils.isNotEmpty(auctionStatus)) sql.append(" and p.auctionStatus = " + auctionStatus);
        if (StringUtils.isNotEmpty(name)) sql.append(" and p.name like '%"+StringUtils.defaultString(name)+"%'");

        sql.append(" order by p.sort desc limit ?,?");
        try {
			int start = (currentPage-1)*pageSize;
			productList = this.query(conn, sql.toString(), new BeanListHandler<Product>(Product.class),start,pageSize);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
        return productList;
    }
    
    
    /**
     * 
     * 获得首页商品列表
     * @param pageSize
     * @param currentPage
     * @param name
     * @return
     */
    public List<Product> getList(String classId, String auctionStatus) {
        List<Product> productList = new ArrayList<Product>();
        Connection conn = this.getConnection();
        StringBuilder sql = new StringBuilder("SELECT * FROM product p  where isvalid=1 and auctionBeginTime-"+System.currentTimeMillis()+"<=1000*60*60*48 ");
        if(StringUtils.isNotEmpty(classId)) sql.append(" and p.classId = " + classId);
        if(StringUtils.isNotEmpty(auctionStatus)) sql.append(" and p.auctionStatus = " + auctionStatus);
        sql.append(" order by p.auctionStatus asc, p.sort desc");
        try {

			productList = this.query(conn, sql.toString(), new BeanListHandler<Product>(Product.class));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
        return productList;
    }


    public List<Product> getList(String classId, String auctionStatus, int top) {
        List<Product> productList = new ArrayList<Product>();
        Connection conn = this.getConnection();
        StringBuilder sql = new StringBuilder("SELECT * FROM product p  where isvalid=1 ");
        if(StringUtils.isNotEmpty(classId)) sql.append(" and p.classId = " + classId);
        if(StringUtils.isNotEmpty(auctionStatus)) sql.append(" and p.auctionStatus = " + auctionStatus);
        sql.append(" order by p.auctionStatus asc, p.sort desc");
        try {

			productList = this.query(conn, sql.toString(), new BeanListHandler<Product>(Product.class));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
        return productList;
    }

    /***
     * 从数据库读取到Map
     * @param id
     * @return
     */
    public void intiMapFromDatabase()
    {
    	List<Product> productList = this.getList(null, null);
    	for(int i=0;i<productList.size();i++)
    	{
    		Product prodcut = (Product)productList.get(i);
    		if(AuctionUtil.getById(prodcut.getId())==null)
    		{
    			AuctionUtil.addProduct(prodcut);
    		}
    	}
    }
    
    /**
     * 根据ID获得
     */
    public Product getById(int id) {
        Connection conn = this.getConnection();
        StringBuilder sql = new StringBuilder("SELECT p.*,pc.name AS className FROM product p LEFT JOIN product_class pc ON p.classId = pc.id where p.id = ?");
		Product product = null;
		try {
			product = this.query(conn, sql.toString(), new BeanHandler<Product>(Product.class),id);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
		return product;
	}

    /**
     * 根据ID删除
     * @param id
     */
    public void deleteById(int id){
        Connection conn = this.getConnection();
		StringBuffer sql = new StringBuffer("delete from product where id = ? ");
		try {
			this.execute(conn, sql.toString(), id);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
    }
    
    /**
     * 保存商品
     * @param product
     */
    public void save(Product product) {
        Connection conn = this.getConnection();
        Object[] pram = {product.getClassId(), product.getName(), product.getAuctionStatus(), System.currentTimeMillis(), product.getAdminId(),
        product.getImagePath(), product.getDescription(), product.getIsvalid(), product.getSmallPic(),product.getSort(),product.getDimension(),product.getGlobalLimited(), product.getSpecification(),product.getMaterial(),product.getPrice()};
        try {
			this.execute(conn, PRODUCT_INSERT, pram);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
	}

    /**
     * 更新商品
     * @param product
     */
    public void update(Product product) {
        Connection conn = this.getConnection();
        Object[] pram = {product.getClassId(), product.getName(), product.getAuctionStatus(), System.currentTimeMillis(), product.getAdminId(),
        product.getImagePath(), product.getDescription(), product.getIsvalid(), product.getSmallPic(), product.getSort(), product.getDimension(), product.getGlobalLimited(), product.getSpecification(), product.getMaterial(), product.getPrice(), product.getId()};
        StringBuffer sql = new StringBuffer("UPDATE product SET classId = ? , NAME = ? , auctionStatus = ? , creationTime = ? , adminId = ? , imagePath = ? , description = ? , isvalid = ? , smallPic = ?, sort = ?, dimension = ?, globalLimited = ?, specification = ?, material = ?, price = ?  WHERE id = ?");
        try {
			this.execute(conn, sql.toString(), pram);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
    }


    /**
	 * 修改状态
	 */
	public void updateIsvalid(String id,Integer isvalid){
		Connection conn = this.getConnection();

		try {
			String sql = "update product set isvalid = ? where id = ?";
			this.execute(conn, sql,new Object[]{isvalid,id});
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(conn);
		}
	}
	
    /**
	 * 修改状态
	 */
	public void updateStatus(Integer id,Integer auctionStatus){
		Connection conn = this.getConnection();

		try {
			String sql = "update product set auctionStatus = ? where id = ?";
			this.execute(conn, sql,new Object[]{auctionStatus,id});
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(conn);
		}
	}
	
	/***
	 * 更新投标
	 */
	public void updateBid(int id,long bidTime,int userId)
	{
		Connection conn = this.getConnection();
		try {
			String sql = "update product set bidRound = bidRound+1,currentPrice=currentPrice+0.01,bidUserId=?,bidTime=? where id = ?";
			this.execute(conn, sql,new Object[]{userId,bidTime,id});
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			close(conn);
		}
	}

    public Product previous(int id) {
        Product productO = getById(id);
        Connection conn = this.getConnection();
        StringBuilder sql = new StringBuilder("SELECT * FROM product p WHERE p.sort < ? ORDER BY p.sort DESC LIMIT 1");
        Object[] pram = {productO.getSort()};
		Product product = null;
		try {
			product = this.query(conn, sql.toString(), new BeanHandler<Product>(Product.class),pram);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
		return product;
    }

    public Product next(int id) {
        Product productO = getById(id);
        Connection conn = this.getConnection();
        StringBuilder sql = new StringBuilder("SELECT * FROM product p WHERE p.sort > ? ORDER BY p.sort ASC LIMIT 1");
        Object[] pram = {productO.getSort()};
		Product product = null;
		try {
			product = this.query(conn, sql.toString(), new BeanHandler<Product>(Product.class),pram);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
		return product;
    }

    /**
     * 得到当前用户已经竞拍到的商品
     * @param userId 用户ID
     * @param days 多少天之内的商品
     * @return
     */
    public List<Product> getBidedProduct(int userId, int days)  {
        List<Product> productList = new ArrayList<Product>();
        Connection conn = this.getConnection();
        StringBuilder sql = new StringBuilder("select p.*,CEIL(("+days*24*60*60*1000+"-(UNIX_TIMESTAMP(NOW())*1000-p.bidTime))) disableMin from product p LEFT JOIN order_items oi ON p.id = oi.productId where oi.id IS NULL and (CEIL((UNIX_TIMESTAMP(NOW())*1000-p.bidTime)) <= "+days+"*24*60*60*1000) and p.bidUserId = " + userId);
        sql.append(" and p.auctionStatus = " + Constants.PRODUCT_AUCTION_STATUS_FROZEN);
        sql.append(" order by p.sort asc");
        try {
			productList = this.query(conn, sql.toString(), new BeanListHandler<Product>(Product.class));
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
        return productList;
    }

    public List<Product> getByOrderId(int orderId) {
        List<Product> productList = new ArrayList<Product>();
        Connection conn = this.getConnection();
        StringBuilder sql = new StringBuilder("select * from order_items oi join product p on oi.productId = p.id where oi.orderId = ? order by p.sort asc");
        try {
			productList = this.query(conn, sql.toString(), new BeanListHandler<Product>(Product.class), orderId);
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			this.close(conn);
		}
        return productList;
    }
}
